const { logger } = require('../utils/logger');

const mockData = {
  users: [
    { user_id: 1, username: 'admin', email: 'admin@example.com', phone: '+6512345678', password_hash: '123456', first_name: 'Admin', last_name: 'User', date_of_birth: new Date('1990-01-01'), gender: 'male', points_balance: 0, membership_level: 'admin', is_active: true, email_verified: true, phone_verified: true, created_at: new Date(), updated_at: new Date(), last_login_at: null },
    { user_id: 2, username: 'admin1', email: 'admin1@example.com', phone: '+6512345679', password_hash: '123456', first_name: 'Admin', last_name: 'User', date_of_birth: new Date('2024-12-01'), gender: 'male', points_balance: 0, membership_level: 'admin', is_active: true, email_verified: true, phone_verified: true, created_at: new Date('2025-08-27T04:42:51.870Z'), updated_at: new Date('2025-08-27T04:42:51.870Z'), last_login_at: null }
  ],
  admins: [],
  orders: [],
  order_items: [],
  user_coupons: [],
  menu_categories: [
    {
      id: 1,
      name: '推荐',
      name_en: 'Must Try',
      description: '推荐菜品',
      description_en: 'Recommended dishes',
      icon: null,
      color: '#FF6B6B',
      is_active: true,
      sort_order: 1,
      created_at: new Date(),
      updated_at: new Date(),
      deleted_at: null
    },
    {
      id: 2,
      name: '促销',
      name_en: 'Promo',
      description: '促销菜品',
      description_en: 'Promotional dishes',
      icon: null,
      color: '#4ECDC4',
      is_active: true,
      sort_order: 2,
      created_at: new Date(),
      updated_at: new Date(),
      deleted_at: null
    },
    {
      id: 3,
      name: '套餐',
      name_en: 'Set',
      description: '套餐组合',
      description_en: 'Set meals',
      icon: null,
      color: '#45B7D1',
      is_active: true,
      sort_order: 3,
      created_at: new Date(),
      updated_at: new Date(),
      deleted_at: null
    },
    {
      id: 4,
      name: '主菜',
      name_en: 'Main Dishes',
      description: '主菜类菜品',
      description_en: 'Main course dishes',
      icon: null,
      color: '#96CEB4',
      is_active: true,
      sort_order: 4,
      created_at: new Date(),
      updated_at: new Date(),
      deleted_at: null
    },
    {
      id: 5,
      name: '配菜',
      name_en: 'Side Dishes',
      description: '配菜小食',
      description_en: 'Side dishes and appetizers',
      icon: null,
      color: '#FFEAA7',
      is_active: true,
      sort_order: 5,
      created_at: new Date(),
      updated_at: new Date(),
      deleted_at: null
    },
    {
      id: 6,
      name: '炸鸡',
      name_en: 'Fried Chicken',
      description: '炸鸡类菜品',
      description_en: 'Fried chicken dishes',
      icon: null,
      color: '#DDA0DD',
      is_active: true,
      sort_order: 6,
      created_at: new Date(),
      updated_at: new Date(),
      deleted_at: null
    },
    {
      id: 7,
      name: '饮品',
      name_en: 'Drinks',
      description: '各种饮品',
      description_en: 'Various beverages',
      icon: null,
      color: '#74B9FF',
      is_active: true,
      sort_order: 7,
      created_at: new Date(),
      updated_at: new Date(),
      deleted_at: null
    },
    {
      id: 8,
      name: '甜品',
      name_en: 'Desserts',
      description: '甜品类菜品',
      description_en: 'Dessert dishes',
      icon: null,
      color: '#FD79A8',
      is_active: true,
      sort_order: 8,
      created_at: new Date(),
      updated_at: new Date(),
      deleted_at: null
    }
  ],
  menu_items: [],
  menu_variants: [],
  menu_options: [],
  coupons: [],
  payments: [],
  // 报表相关数据
  reportConfigs: [],
  reportPermissions: [],
  reportExports: [],
  reportSubscriptions: [],
  reportSubscriptionLogs: [],
  // 导入任务相关数据
  import_jobs: [],
  // 邀请记录相关数据
  invite_records: [
    {
      id: 1,
      inviter_id: 1,
      invitee_id: 2,
      invite_code: 'INV001',
      status: 'completed',
      coupon_status: 'issued',
      inviter_coupon_amount: 10.00,
      invitee_coupon_amount: 5.00,
      bonus_coupon_amount: 0.00,
      invite_date: new Date('2025-01-15'),
      completion_date: new Date('2025-01-16'),
      created_at: new Date('2025-01-15'),
      updated_at: new Date('2025-01-16')
    },
    {
      id: 2,
      inviter_id: 1,
      invitee_id: null,
      invite_code: 'INV002',
      status: 'pending',
      coupon_status: 'partial',
      inviter_coupon_amount: 10.00,
      invitee_coupon_amount: 5.00,
      bonus_coupon_amount: 2.00,
      invite_date: new Date('2025-01-20'),
      completion_date: null,
      created_at: new Date('2025-01-20'),
      updated_at: new Date('2025-01-20')
    },
    {
      id: 3,
      inviter_id: 2,
      invitee_id: null,
      invite_code: 'INV003',
      status: 'pending',
      coupon_status: 'pending',
      inviter_coupon_amount: 10.00,
      invitee_coupon_amount: 5.00,
      bonus_coupon_amount: 0.00,
      invite_date: new Date(),
      completion_date: null,
      created_at: new Date(),
      updated_at: new Date()
    }
  ]
};

// 模拟查询方法
const mockQuery = async (sql, params = []) => {
  logger.debug('🔍 Mock DB Query:', sql, params);
  
  // 模拟延迟
  await new Promise(resolve => setTimeout(resolve, 100));
  
  // 简单的SQL解析和模拟响应
  const sqlLower = sql.toLowerCase().trim();
  
  if (sqlLower.includes('select') && sqlLower.includes('users')) {
    // 精确判断 WHERE 子句
    if (sqlLower.includes(' where email = ?')) {
      const email = params[0];
      const user = mockData.users.find(u => u.email === email);
      return user ? [user] : [];
    }
    if (sqlLower.includes(' where user_id = ?')) {
      const userId = params[0];
      let user = mockData.users.find(u => String(u.user_id) === String(userId));
      // 如果查询包含 is_active = true 条件，需要检查用户状态
      if (user && sqlLower.includes('is_active = true') && !user.is_active) {
        user = null;
      }
      return user ? [user] : [];
    }
    if (sqlLower.includes(' where phone = ?')) {
      const phone = params[0];
      const user = mockData.users.find(u => u.phone === phone);
      return user ? [user] : [];
    }
    return mockData.users;
  }
  
  // ======= 菜单分类 =======
  if (sqlLower.includes('select') && sqlLower.includes('menu_categories')) {
    // 检查是否为统计查询 (getCategoriesWithItemCount)
    if (sqlLower.includes('count(') && sqlLower.includes('left join') && sqlLower.includes('menu_items')) {
      // 处理 getCategoriesWithItemCount 查询
      const categories = mockData.menu_categories
        .filter(c => !c.deleted_at)
        .map(c => ({
          id: c.id,
          name: c.name,
          name_en: c.name_en,
          description: c.description,
          description_en: c.description_en,
          icon: c.icon || null,
          color: c.color || null,
          is_active: c.is_active ?? true,
          sort_order: c.sort_order ?? 0,
          created_at: c.created_at || new Date(),
          updated_at: c.updated_at || new Date(),
          deleted_at: c.deleted_at || null,
          item_count: mockData.menu_items.filter(i => i.category_id === c.id && !i.deleted_at).length,
          available_count: mockData.menu_items.filter(i => i.category_id === c.id && !i.deleted_at && i.is_available).length
        }))
        .sort((a, b) => a.sort_order - b.sort_order);
      
      return categories;
    }
    
    // 普通分类查询
    let categories = mockData.menu_categories
      .map(c => ({
        id: c.id,
        name: c.name,
        name_en: c.name_en,
        description: c.description,
        description_en: c.description_en,
        icon: c.icon || null,
        color: c.color || null,
        is_active: c.is_active ?? true,
        sort_order: c.sort_order ?? 0,
        created_at: c.created_at || new Date(),
        updated_at: c.updated_at || new Date(),
        deleted_at: c.deleted_at || null
      }));

    // 支持 WHERE id = ? 精确查询
    if (sqlLower.includes(' where id = ?')) {
      const idParam = params[0];
      let result = categories.filter(c => c.id == idParam);
      if (sqlLower.includes('deleted_at is null')) {
        result = result.filter(c => !c.deleted_at);
      }
      return result;
    }

    // 支持 deleted_at 过滤
    if (sqlLower.includes('deleted_at is null')) {
      categories = categories.filter(c => !c.deleted_at);
    }

    // is_active 过滤
    if (sqlLower.includes('is_active = ?')) {
      const isActiveParam = params[0];
      categories = categories.filter(c => !!c.is_active === !!isActiveParam);
    }

    // 排序：解析 ORDER BY 字段与方向
    if (sqlLower.includes('order by')) {
      const isDesc = sqlLower.includes(' desc');
      let key = 'sort_order';
      if (sqlLower.includes(' order by name')) key = 'name';
      else if (sqlLower.includes(' order by id')) key = 'id';
      else if (sqlLower.includes(' order by sort_order')) key = 'sort_order';
      else if (sqlLower.includes(' order by created_at')) key = 'created_at';
      else if (sqlLower.includes(' order by updated_at')) key = 'updated_at';

      categories.sort((a, b) => {
        const av = a[key];
        const bv = b[key];
        const cmp = av > bv ? 1 : av < bv ? -1 : 0;
        return isDesc ? -cmp : cmp;
      });
    } else {
      categories.sort((a, b) => a.sort_order - b.sort_order);
    }

    return categories;
  }

  // ======= 菜单项 =======
  if (sqlLower.includes('select') && sqlLower.includes('menu_items')) {
    // 菜品统计查询 (getStats)
    if (sqlLower.includes('count(*) as total') && sqlLower.includes('avg(price)')) {
      const items = mockData.menu_items.filter(i => !i.deleted_at);
      const available = items.filter(i => i.is_available);
      const featured = items.filter(i => i.is_featured);
      const prices = items.map(i => i.price).filter(p => p > 0);
      
      return [{
        total: items.length,
        available: available.length,
        featured: featured.length,
        avg_price: prices.length ? (prices.reduce((a, b) => a + b, 0) / prices.length) : 0,
        min_price: prices.length ? Math.min(...prices) : 0,
        max_price: prices.length ? Math.max(...prices) : 0
      }];
    }

    // 构造标准化 items 集合（保持JSON字段为字符串，供模型JSON.parse）
    let items = mockData.menu_items.map(i => ({
      id: i.id,
      name: i.name,
      name_en: i.name_en,
      description: i.description,
      description_en: i.description_en,
      price: i.price,
      category_id: i.category_id,
      image_url: i.image_url || null,
      is_available: i.is_available ?? true,
      is_featured: i.is_featured ?? false,
      preparation_time: i.preparation_time || null,
      calories: i.calories || null,
      spice_level: i.spice_level || null,
      allergens: i.allergens || null,
      ingredients: i.ingredients || null,
      main_ingredients: i.main_ingredients || null,
      nutritional_info: i.nutritional_info || null,
      tags: i.tags || null,
      sort_order: i.sort_order ?? 0,
      normalized_name: i.normalized_name || null,
      created_at: i.created_at || new Date(),
      updated_at: i.updated_at || new Date(),
      deleted_at: i.deleted_at || null
    }));

    // 支持 deleted_at 过滤
    if (sqlLower.includes('deleted_at is null')) {
      items = items.filter(i => !i.deleted_at);
    }

    // 解析 where 条件顺序，与模型构造顺序保持一致
    // category_id -> is_available -> is_featured -> search (4次 like)
    let p = 0;
    if (sqlLower.includes('category_id = ?')) {
      const categoryId = params[p++];
      items = items.filter(i => i.category_id == categoryId);
    }
    if (sqlLower.includes('is_available = ?')) {
      const isAvailable = params[p++];
      items = items.filter(i => (!!i.is_available) === (!!isAvailable));
    }
    if (sqlLower.includes('is_featured = ?')) {
      const isFeatured = params[p++];
      items = items.filter(i => (!!i.is_featured) === (!!isFeatured));
    }
    if (sqlLower.includes(' like ?')) {
      const searchTerm = (params[p++] || '').toString().replace(/%/g, '').toLowerCase();
      // 跳过重复的三个 like 占位
      p += 3;
      if (searchTerm) {
        items = items.filter(i =>
          (i.name && i.name.toLowerCase().includes(searchTerm)) ||
          (i.name_en && i.name_en.toLowerCase().includes(searchTerm)) ||
          (i.description && i.description.toLowerCase().includes(searchTerm)) ||
          (i.description_en && i.description_en.toLowerCase().includes(searchTerm))
        );
      }
    }

    // findById 的精确匹配
    if (sqlLower.includes(' where id = ?')) {
      const idParam = params[0];
      let result = items.filter(i => i.id == idParam);
      if (sqlLower.includes('deleted_at is null')) {
        result = result.filter(i => !i.deleted_at);
      }
      return result;
    }

    // V2导入系统：通过normalized_name查找现有记录
    if (sqlLower.includes(' where normalized_name = ?')) {
      const normalizedName = params[0];
      let result = items.filter(i => i.normalized_name === normalizedName);
      if (sqlLower.includes('deleted_at is null')) {
        result = result.filter(i => !i.deleted_at);
      }
      return result;
    }

    // V2导入系统：通过name_en查找现有记录
    if (sqlLower.includes(' where name_en = ?')) {
      const nameEn = params[0];
      let result = items.filter(i => i.name_en === nameEn);
      if (sqlLower.includes('deleted_at is null')) {
        result = result.filter(i => !i.deleted_at);
      }
      return result;
    }

    // COUNT(*) 聚合
    if (sqlLower.includes('count(*)')) {
      // 兼容别名 total 或 count
      const aliasIsCount = sqlLower.includes(' as count');
      const result = aliasIsCount ? { count: items.length } : { total: items.length };
      return [result];
    }

    // 排序（支持 ORDER BY 字段与方向）
    if (sqlLower.includes('order by')) {
      const isDesc = sqlLower.includes(' desc');
      let key = 'sort_order';
      if (sqlLower.includes(' order by price')) key = 'price';
      else if (sqlLower.includes(' order by name')) key = 'name';
      else if (sqlLower.includes(' order by id')) key = 'id';
      else if (sqlLower.includes(' order by sort_order')) key = 'sort_order';
      else if (sqlLower.includes(' order by created_at')) key = 'created_at';
      else if (sqlLower.includes(' order by updated_at')) key = 'updated_at';

      items.sort((a, b) => {
        const av = a[key];
        const bv = b[key];
        const cmp = av > bv ? 1 : av < bv ? -1 : 0;
        return isDesc ? -cmp : cmp;
      });
    } else {
      items.sort((a, b) => a.sort_order - b.sort_order);
    }

    // 分页（LIMIT ? OFFSET ?）
    if (sqlLower.includes(' limit ? offset ?')) {
      const limit = Number(params[params.length - 2]) || items.length;
      const offset = Number(params[params.length - 1]) || 0;
      return items.slice(offset, offset + limit);
    }

    return items;
  }
  
  if (sqlLower.includes('select') && sqlLower.includes('admins')) {
    if (sqlLower.includes(' where user_id = ?')) {
      const userId = params[0];
      return mockData.admins.filter(a => String(a.user_id) === String(userId) && a.is_active);
    }
    return mockData.admins.filter(a => a.is_active);
  }
  
  if (sqlLower.includes('insert into users')) {
    const newUser = {
      user_id: mockData.users.length + 1,
      username: params[0],
      email: params[1],
      phone: params[2],
      password_hash: params[3],
      first_name: params[4] || '',
      last_name: params[5] || '',
      date_of_birth: params[6] || null,
      gender: params[7] || null,
      points_balance: 0,
      membership_level: 'bronze',
      is_active: true,
      email_verified: false,
      phone_verified: false,
      created_at: new Date(),
      updated_at: new Date(),
      last_login_at: null
    };
    mockData.users.push(newUser);
    return { insertId: newUser.user_id, affectedRows: 1 };
  }
  
  if (sqlLower.includes('insert into menu_categories')) {
    // INSERT INTO menu_categories
    const newId = Math.max(...mockData.menu_categories.map(c => c.id), 0) + 1;
    const newCategory = {
      id: newId,
      name: params[0],
      name_en: params[1] || '',
      description: params[2] || '',
      description_en: params[3] || '',
      icon: params[4] || null,
      color: params[5] || null,
      is_active: params[6] !== undefined ? params[6] : true,
      sort_order: params[7] || newId,
      created_at: new Date(),
      updated_at: new Date()
    };
    mockData.menu_categories.push(newCategory);
    return { insertId: newId, affectedRows: 1 };
  }

  if (sqlLower.includes('insert into menu_items')) {
    // INSERT INTO menu_items
    // 如果菜单项为空，从1开始；否则找到最大ID+1
    let newId = 1;
    if (mockData.menu_items.length > 0) {
      // 查找当前最小ID，如果最小ID大于1，则从1开始
      const minId = Math.min(...mockData.menu_items.map(i => i.id));
      if (minId <= 1) {
        // 如果已经有ID为1的项，则找到最大ID+1
        newId = Math.max(...mockData.menu_items.map(i => i.id), 0) + 1;
      }
    }
    const newItem = {
      id: newId,
      name: params[0],
      name_en: params[1] || '',
      description: params[2] || '',
      description_en: params[3] || '',
      price: params[4],
      category_id: params[5],
      image_url: params[6] || null,
      is_available: params[7] !== undefined ? params[7] : true,
      is_featured: params[8] !== undefined ? params[8] : false,
      preparation_time: params[9] || null,
      calories: params[10] || null,
      spice_level: params[11] || null,
      allergens: params[12] || null,  // 已经是 JSON.stringify 字符串
      ingredients: params[13] || null,  // 已经是 JSON.stringify 字符串
      nutritional_info: params[14] || null,  // 已经是 JSON.stringify 字符串
      tags: params[15] || null,  // 已经是 JSON.stringify 字符串
      sort_order: params[16] || newId,
      normalized_name: params[17] || '',  // V2三表结构新增字段
      created_at: new Date(),
      updated_at: new Date()
    };
    mockData.menu_items.push(newItem);
    return { insertId: newId, affectedRows: 1 };
  }

  // ======= 菜单变体 =======
  if (sqlLower.includes('insert into menu_variants')) {
    const newId = Math.max(0, ...mockData.menu_variants.map(v => v.id || 0)) + 1;
    const newVariant = {
      id: newId,
      menu_item_id: params[0],
      variant_name: params[1],
      variant_name_en: params[2],
      price: params[3],
      sort_order: params[4] || 9999,
      created_at: new Date(),
      updated_at: new Date()
    };
    mockData.menu_variants.push(newVariant);
    return { insertId: newId, affectedRows: 1 };
  }

  if (sqlLower.includes('select') && sqlLower.includes('menu_variants')) {
    if (sqlLower.includes(' where menu_item_id = ?')) {
      const menuItemId = params[0];
      return mockData.menu_variants.filter(v => String(v.menu_item_id) === String(menuItemId) && !v.deleted_at);
    }
    if (sqlLower.includes(' where id = ?')) {
      const id = params[0];
      const variant = mockData.menu_variants.find(v => String(v.id) === String(id) && !v.deleted_at);
      return variant ? [variant] : [];
    }
    return mockData.menu_variants.filter(v => !v.deleted_at);
  }

  if (sqlLower.includes('update menu_variants')) {
    const idParam = params[params.length - 1];
    const variantIndex = mockData.menu_variants.findIndex(v => String(v.id) === String(idParam));
    
    if (variantIndex !== -1) {
      // 软删除
      if (sqlLower.includes('deleted_at = now()')) {
        mockData.menu_variants[variantIndex].deleted_at = new Date();
        return { affectedRows: 1 };
      }

      // 动态字段更新
      const fieldList = ['variant_name', 'variant_name_en', 'price', 'sort_order'];
      let p = 0;
      for (const f of fieldList) {
        if (sqlLower.includes(`${f} = ?`)) {
          mockData.menu_variants[variantIndex][f] = params[p++];
        }
      }
      mockData.menu_variants[variantIndex].updated_at = new Date();
      return { affectedRows: 1 };
    }
    return { affectedRows: 0 };
  }

  // ======= 菜单选项 =======
  if (sqlLower.includes('insert into menu_options')) {
    const newId = Math.max(0, ...mockData.menu_options.map(o => o.id || 0)) + 1;
    const newOption = {
      id: newId,
      menu_item_id: params[0],
      option_group: params[1],
      option_value: params[2],
      price_delta: params[3] || 0,
      is_required: params[4] !== undefined ? params[4] : false,
      sort_order: params[5] || 9999,
      created_at: new Date(),
      updated_at: new Date()
    };
    mockData.menu_options.push(newOption);
    return { insertId: newId, affectedRows: 1 };
  }

  if (sqlLower.includes('select') && sqlLower.includes('menu_options')) {
    if (sqlLower.includes(' where menu_item_id = ?')) {
      const menuItemId = params[0];
      return mockData.menu_options.filter(o => String(o.menu_item_id) === String(menuItemId) && !o.deleted_at);
    }
    if (sqlLower.includes(' where id = ?')) {
      const id = params[0];
      const option = mockData.menu_options.find(o => String(o.id) === String(id) && !o.deleted_at);
      return option ? [option] : [];
    }
    return mockData.menu_options.filter(o => !o.deleted_at);
  }

  if (sqlLower.includes('update menu_options')) {
    const idParam = params[params.length - 1];
    const optionIndex = mockData.menu_options.findIndex(o => String(o.id) === String(idParam));
    
    if (optionIndex !== -1) {
      // 软删除
      if (sqlLower.includes('deleted_at = now()')) {
        mockData.menu_options[optionIndex].deleted_at = new Date();
        return { affectedRows: 1 };
      }

      // 动态字段更新
      const fieldList = ['option_group', 'option_value', 'price_delta', 'is_required', 'sort_order'];
      let p = 0;
      for (const f of fieldList) {
        if (sqlLower.includes(`${f} = ?`)) {
          mockData.menu_options[optionIndex][f] = params[p++];
        }
      }
      mockData.menu_options[optionIndex].updated_at = new Date();
      return { affectedRows: 1 };
    }
    return { affectedRows: 0 };
  }

  // ======= 导入任务 =======
  if (sqlLower.includes('select') && sqlLower.includes('import_jobs')) {
    if (sqlLower.includes(' where job_id = ?')) {
      const jobId = params[0];
      const job = mockData.import_jobs.find(j => j.job_id === jobId);
      return job ? [job] : [];
    }
    if (sqlLower.includes(' where id = ?')) {
      const id = params[0];
      const job = mockData.import_jobs.find(j => String(j.id) === String(id));
      return job ? [job] : [];
    }
    return mockData.import_jobs;
  }

  if (sqlLower.includes('insert into import_jobs')) {
    // 参数顺序: job_id, import_type, admin_id, file_name, file_path, file_size, status
    const newId = Math.max(0, ...mockData.import_jobs.map(j => j.id || 0)) + 1;
    const newJob = {
      id: newId,
      job_id: params[0],
      import_type: params[1],
      admin_id: params[2],
      file_name: params[3],
      file_path: params[4],
      file_size: params[5] || 0,
      status: params[6] || 'uploaded',
      total_records: 0,
      processed_records: 0,
      success_records: 0,
      error_records: 0,
      validation_errors: null,
      snapshot_data: null,
      dry_run_result: null,
      created_by: params[2], // admin_id
      created_at: new Date(),
      updated_at: new Date(),
      completed_at: null
    };
    mockData.import_jobs.push(newJob);
    return { insertId: newJob.id, affectedRows: 1 };
  }

  if (sqlLower.includes('update import_jobs')) {
    let targetIndex = -1;
    const lastParam = params[params.length - 1];
    if (sqlLower.includes(' where id = ?')) {
      targetIndex = mockData.import_jobs.findIndex(j => String(j.id) === String(lastParam));
    } else if (sqlLower.includes(' where job_id = ?')) {
      targetIndex = mockData.import_jobs.findIndex(j => j.job_id === lastParam);
    } else {
      // 兼容：优先按 id，再按 job_id
      targetIndex = mockData.import_jobs.findIndex(j => String(j.id) === String(lastParam) || j.job_id === lastParam);
    }
    
    if (targetIndex !== -1) {
      // 动态字段更新
      const fieldList = [
        'status', 'total_records', 'processed_records', 'success_records',
        'error_records', 'validation_errors', 'snapshot_data', 'completed_at',
        // 额外兼容字段（验证报告路径等）
        'validation_report_json', 'validation_report_csv', 'error_file_path'
      ];
      let p = 0;
      for (const f of fieldList) {
        if (sqlLower.includes(`${f} = ?`)) {
          mockData.import_jobs[targetIndex][f] = params[p++];
        }
      }
      mockData.import_jobs[targetIndex].updated_at = new Date();
      return { affectedRows: 1 };
    }
    return { affectedRows: 0 };
  }

  if (sqlLower.includes('update menu_categories')) {
    // 获取ID（最后一个参数）
    const idParam = params[params.length - 1];
    const categoryIndex = mockData.menu_categories.findIndex(c => c.id == idParam);
    
    if (categoryIndex !== -1) {
      // 软删除
      if (sqlLower.includes('deleted_at = now()')) {
        mockData.menu_categories[categoryIndex].deleted_at = new Date();
        return { affectedRows: 1 };
      }

      // 动态字段更新：按照SQL出现的字段顺序依次取参数
      const fieldList = [
        'name', 'name_en', 'description', 'description_en',
        'icon', 'color', 'is_active', 'sort_order'
      ];
      let p = 0;
      for (const f of fieldList) {
        if (sqlLower.includes(`${f} = ?`)) {
          mockData.menu_categories[categoryIndex][f] = params[p++];
        }
      }
      mockData.menu_categories[categoryIndex].updated_at = new Date();
      return { affectedRows: 1 };
    }
    return { affectedRows: 0 };
  }

  if (sqlLower.includes('update menu_items')) {
    const idParam = params[params.length - 1];
    const itemIndex = mockData.menu_items.findIndex(i => i.id == idParam);
    
    if (itemIndex !== -1) {
      // 软删除
      if (sqlLower.includes('deleted_at = now()')) {
        mockData.menu_items[itemIndex].deleted_at = new Date();
        return { affectedRows: 1 };
      }

      // 更新可用性/排序快捷场景：仅当 SET 中只有一个占位符字段时才走快捷分支（updateAvailability / sort 单字段场景），
      // 避免覆盖通用更新导致诸如 price 无法保存的问题。
      const setPart = (() => {
        const setIdx = sqlLower.indexOf(' set ');
        const whereIdx = sqlLower.indexOf(' where ');
        if (setIdx !== -1 && whereIdx !== -1 && whereIdx > setIdx) {
          return sqlLower.slice(setIdx + 5, whereIdx);
        }
        return '';
      })();
      const placeholderCount = (setPart.split('?').length - 1);
      const isSingleFieldUpdate = placeholderCount === 1;

      // 更新可用性场景
      if (isSingleFieldUpdate && setPart.includes('is_available = ?')) {
        mockData.menu_items[itemIndex].is_available = params[0];
        mockData.menu_items[itemIndex].updated_at = new Date();
        return { affectedRows: 1 };
      }
      
      // 更新排序场景
      if (isSingleFieldUpdate && setPart.includes('sort_order = ?')) {
        mockData.menu_items[itemIndex].sort_order = params[0];
        mockData.menu_items[itemIndex].updated_at = new Date();
        return { affectedRows: 1 };
      }
      
      // 通用更新 - 解析SET子句中的字段和参数
      const setClause = setPart.trim();
      const setFields = setClause.split(',').map(s => s.trim());
      
      // 更新字段（排除最后一个参数，它是WHERE条件中的ID）
      const updateParams = params.slice(0, -1);
      
      for (let i = 0; i < setFields.length && i < updateParams.length; i++) {
        const fieldPart = setFields[i];
        const fieldMatch = fieldPart.match(/^(\w+)\s*=\s*\?$/);
        if (fieldMatch) {
          const fieldName = fieldMatch[1];
          mockData.menu_items[itemIndex][fieldName] = updateParams[i];
        }
      }
      
      mockData.menu_items[itemIndex].updated_at = new Date();
      return { affectedRows: 1 };
    }
    return { affectedRows: 0 };
  }

  // ======= 订单与订单项 =======
  // 用户订单历史（JOIN 订单项计数）
  if (sqlLower.includes(' from orders o') && sqlLower.includes('left join order_items')) {
    let p = 0;
    let userId = null;
    let status = null;
    if (sqlLower.includes(' where o.user_id = ?')) {
      userId = params[p++];
    }
    if (sqlLower.includes(' and o.status = ?')) {
      status = params[p++];
    }

    const rows = mockData.orders.filter(o => (
      userId == null || String(o.user_id) === String(userId)
    ) && (
      status == null || o.status === status
    ));

    const countItems = (orderId) => mockData.order_items.filter(oi => String(oi.order_id) === String(orderId)).length;

    let results = rows.map(o => ({
      order_id: o.order_id,
      order_number: o.order_number,
      total_amount: o.total_amount,
      status: o.status,
      order_type: o.order_type,
      created_at: o.created_at,
      updated_at: o.updated_at,
      item_count: countItems(o.order_id)
    }));

    if (sqlLower.includes(' order by ') && sqlLower.includes('o.created_at') && sqlLower.includes(' desc')) {
      results.sort((a, b) => new Date(b.created_at) - new Date(a.created_at));
    }

    if (sqlLower.includes(' limit ? offset ?')) {
      const limit = Number(params[params.length - 2]) || results.length;
      const offset = Number(params[params.length - 1]) || 0;
      return results.slice(offset, offset + limit);
    }

    return results;
  }

  // 用户订单总数统计（COUNT DISTINCT）
  if (sqlLower.includes('select count(distinct o.order_id) as total from orders o')) {
    let p = 0;
    let userId = null;
    let status = null;
    if (sqlLower.includes(' where o.user_id = ?')) userId = params[p++];
    if (sqlLower.includes(' and o.status = ?')) status = params[p++];
    const rows = mockData.orders.filter(o => (
      userId == null || String(o.user_id) === String(userId)
    ) && (
      status == null || o.status === status
    ));
    return [{ total: rows.length }];
  }

  // 热门菜品聚合（Order.getPopularItems）
  if (
    sqlLower.includes(' from order_items oi') &&
    sqlLower.includes(' join menu_items mi') &&
    sqlLower.includes(' join orders o') &&
    sqlLower.includes(' group by mi.id')
  ) {
    let p = 0;
    let startDate = null;
    let endDate = null;
    let limit = 10;

    if (sqlLower.includes(' o.created_at >= ?')) {
      startDate = params[p++];
    }
    if (sqlLower.includes(' o.created_at <= ?')) {
      endDate = params[p++];
    }
    if (sqlLower.includes(' limit ?')) {
      limit = parseInt(params[p++], 10) || limit;
    }

    const startTime = startDate ? new Date(startDate).getTime() : null;
    const endTime = endDate ? new Date(endDate).getTime() : null;

    // 过滤订单：仅已完成，且满足时间范围
    const validOrderIds = new Set(
      mockData.orders
        .filter(o => o.status === 'completed')
        .filter(o => (startTime == null || new Date(o.created_at).getTime() >= startTime))
        .filter(o => (endTime == null || new Date(o.created_at).getTime() <= endTime))
        .map(o => o.order_id)
    );

    // 聚合订单项
    const aggMap = new Map(); // key: menu_item_id -> { qty, revenue, orderIds:Set }
    for (const oi of mockData.order_items) {
      if (!validOrderIds.has(oi.order_id)) continue;
      const mi = mockData.menu_items.find(m => String(m.id) === String(oi.menu_item_id));
      if (!mi) continue;
      const key = String(mi.id);
      if (!aggMap.has(key)) {
        aggMap.set(key, { qty: 0, revenue: 0, orderIds: new Set(), mi });
      }
      const rec = aggMap.get(key);
      rec.qty += Number(oi.quantity) || 0;
      rec.revenue += Number(oi.total_price) || 0;
      rec.orderIds.add(oi.order_id);
    }

    // 生成结果并排序/限制
    let results = Array.from(aggMap.values()).map(({ qty, revenue, orderIds, mi }) => ({
      id: mi.id,
      name: mi.name,
      name_en: mi.name_en,
      image_url: mi.image_url,
      total_quantity: qty,
      order_count: orderIds.size,
      total_revenue: revenue
    }));

    results.sort((a, b) => (b.total_quantity - a.total_quantity) || (a.id - b.id));
    if (limit != null) {
      results = results.slice(0, Math.max(0, parseInt(limit, 10) || 0));
    }
    return results;
  }

  // 分类销量统计聚合（Order.getCategorySales）
  if (
    sqlLower.includes(' from order_items oi') &&
    sqlLower.includes(' join menu_items mi') &&
    sqlLower.includes(' join orders o') &&
    sqlLower.includes(' group by mi.category_id')
  ) {
    let p = 0;
    let startDate = null;
    let endDate = null;
    let limit = 10;

    if (sqlLower.includes(' o.created_at >= ?')) {
      startDate = params[p++];
    }
    if (sqlLower.includes(' o.created_at <= ?')) {
      endDate = params[p++];
    }
    if (sqlLower.includes(' limit ?')) {
      limit = parseInt(params[p++], 10) || limit;
    }

    const startTime = startDate ? new Date(startDate).getTime() : null;
    const endTime = endDate ? new Date(endDate).getTime() : null;

    const validOrderIds = new Set(
      mockData.orders
        .filter(o => o.status === 'completed')
        .filter(o => (startTime == null || new Date(o.created_at).getTime() >= startTime))
        .filter(o => (endTime == null || new Date(o.created_at).getTime() <= endTime))
        .map(o => o.order_id)
    );

    const aggMap = new Map(); // key: category_id -> { qty, revenue, orderIds:Set }
    for (const oi of mockData.order_items) {
      if (!validOrderIds.has(oi.order_id)) continue;
      const mi = mockData.menu_items.find(m => String(m.id) === String(oi.menu_item_id));
      if (!mi) continue;
      const key = String(mi.category_id);
      if (!aggMap.has(key)) {
        aggMap.set(key, { qty: 0, revenue: 0, orderIds: new Set(), category_id: mi.category_id });
      }
      const rec = aggMap.get(key);
      rec.qty += Number(oi.quantity) || 0;
      rec.revenue += Number(oi.total_price) || 0;
      rec.orderIds.add(oi.order_id);
    }

    let results = Array.from(aggMap.values()).map(({ qty, revenue, orderIds, category_id }) => ({
      category_id,
      total_quantity: qty,
      order_count: orderIds.size,
      total_revenue: revenue
    }));

    // 与 SQL 一致按 total_revenue 排序
    results.sort((a, b) => (b.total_revenue - a.total_revenue) || (a.category_id - b.category_id));
    if (limit != null) {
      results = results.slice(0, Math.max(0, parseInt(limit, 10) || 0));
    }
    return results;
  }

  // 订单统计查询（Order.getStatistics）
  if (
    sqlLower.includes('count(*) as total_orders') &&
    sqlLower.includes("count(case when status = 'completed' then 1 end)") &&
    sqlLower.includes("count(case when status = 'cancelled' then 1 end)") &&
    sqlLower.includes("count(case when status = 'pending' then 1 end)")
  ) {
    let p = 0;
    let startDate = null;
    let endDate = null;
    let userId = null;

    if (sqlLower.includes(' and created_at >= ?')) {
      startDate = params[p++];
    }
    if (sqlLower.includes(' and created_at <= ?')) {
      endDate = params[p++];
    }
    if (sqlLower.includes(' and user_id = ?')) {
      userId = params[p++];
    }

    // 过滤订单
    let filteredOrders = mockData.orders.filter(o => {
      // 用户过滤
      if (userId && String(o.user_id) !== String(userId)) return false;
      
      // 时间过滤
      if (startDate || endDate) {
        const orderTime = new Date(o.created_at);
        if (startDate && orderTime < new Date(startDate)) return false;
        if (endDate && orderTime > new Date(endDate)) return false;
      }
      
      return true;
    });

    // 计算统计数据
    const totalOrders = filteredOrders.length;
    const completedOrders = filteredOrders.filter(o => o.status === 'completed').length;
    const cancelledOrders = filteredOrders.filter(o => o.status === 'cancelled').length;
    const pendingOrders = filteredOrders.filter(o => o.status === 'pending').length;
    
    const completedOrdersData = filteredOrders.filter(o => o.status === 'completed');
    const totalRevenue = completedOrdersData.reduce((sum, o) => sum + (o.total_amount || 0), 0);
    const avgOrderValue = completedOrdersData.length > 0 ? totalRevenue / completedOrdersData.length : null;

    return [{
      total_orders: totalOrders,
      completed_orders: completedOrders,
      cancelled_orders: cancelledOrders,
      pending_orders: pendingOrders,
      total_revenue: totalRevenue,
      avg_order_value: avgOrderValue
    }];
  }

  // 订单详情：订单项 JOIN 菜品
  if (sqlLower.includes(' from order_items oi') && sqlLower.includes(' join menu_items')) {
    let p = 0;
    let orderId = null;
    if (sqlLower.includes(' where oi.order_id = ?')) {
      orderId = params[p++];
    }

    let items = mockData.order_items
      .filter(oi => orderId == null || String(oi.order_id) === String(orderId))
      .map(oi => {
        const mi = mockData.menu_items.find(m => String(m.id) === String(oi.menu_item_id)) || {};
        return {
          id: oi.item_id ?? oi.id ?? null,
          order_id: oi.order_id,
          menu_item_id: oi.menu_item_id,
          quantity: oi.quantity,
          unit_price: oi.unit_price,
          total_price: oi.total_price,
          notes: oi.notes ?? oi.special_requests ?? null,
          created_at: oi.created_at,
          item_name: mi.name || null,
          item_name_en: mi.name_en || null,
          item_image: mi.image_url || null
        };
      });

    if (sqlLower.includes(' order by oi.id')) {
      items.sort((a, b) => (a.id || 0) - (b.id || 0));
    }

    return items;
  }

  // 通用订单查询（适配 Order.js 的 SELECT *）
  if (sqlLower.includes('select') && sqlLower.includes(' from orders') && !sqlLower.includes(' from orders o')) {
    const mapOrder = (o) => ({
      id: o.id,
      order_number: o.order_number,
      user_id: o.user_id,
      status: o.status,
      order_type: o.order_type,
      table_number: o.table_number ?? null,
      delivery_address: o.delivery_address ?? null,
      delivery_phone: o.delivery_phone ?? null,
      delivery_notes: o.delivery_notes ?? (o.special_instructions ?? null),
      subtotal: o.subtotal ?? 0,
      tax_amount: o.tax_amount ?? 0,
      discount_amount: o.discount_amount ?? 0,
      delivery_fee: o.delivery_fee ?? 0,
      total_amount: o.total_amount ?? 0,
      payment_method: o.payment_method ?? null,
      payment_status: o.payment_status ?? null,
      payment_id: o.payment_id ?? o.transaction_id ?? null,
      coupon_id: o.coupon_id ?? null,
      notes: o.notes ?? o.special_instructions ?? null,
      estimated_time: o.estimated_time ?? o.estimated_delivery_time ?? null,
      completed_at: o.completed_at ?? null,
      cancelled_at: o.cancelled_at ?? null,
      created_at: o.created_at,
      updated_at: o.updated_at
    });

    // 精确条件优先
    if (sqlLower.includes(' where id = ?') || sqlLower.includes(' where order_id = ?')) {
      const idParam = params[0];
      const o = mockData.orders.find(x => String(x.id) === String(idParam));
      return o ? [mapOrder(o)] : [];
    }
    if (sqlLower.includes(' where order_number = ?')) {
      const v = params[0];
      const o = mockData.orders.find(x => x.order_number === v);
      return o ? [mapOrder(o)] : [];
    }

    let orders = [...mockData.orders];
    let p = 0;
    if (sqlLower.includes(' where user_id = ?')) {
      const v = params[p++];
      orders = orders.filter(o => String(o.user_id) === String(v));
    }
    if (sqlLower.includes(' and status = ?')) {
      const v = params[p++];
      orders = orders.filter(o => o.status === v);
    }
    if (sqlLower.includes(' and order_type = ?')) {
      const v = params[p++];
      orders = orders.filter(o => o.order_type === v);
    }
    if (sqlLower.includes(' and created_at >= ?')) {
      const v = new Date(params[p++]);
      orders = orders.filter(o => new Date(o.created_at) >= v);
    }
    if (sqlLower.includes(' and created_at <= ?')) {
      const v = new Date(params[p++]);
      orders = orders.filter(o => new Date(o.created_at) <= v);
    }

    if (sqlLower.includes(' order by ')) {
      const isDesc = sqlLower.includes(' desc');
      let key = 'created_at';
      if (sqlLower.includes(' order by id')) key = 'order_id';
      else if (sqlLower.includes(' order by created_at')) key = 'created_at';
      else if (sqlLower.includes(' order by updated_at')) key = 'updated_at';
      else if (sqlLower.includes(' order by total_amount')) key = 'total_amount';
      else if (sqlLower.includes(' order by order_number')) key = 'order_number';

      orders.sort((a, b) => {
        const av = a[key];
        const bv = b[key];
        const cmp = av > bv ? 1 : av < bv ? -1 : 0;
        return isDesc ? -cmp : cmp;
      });
    }

    if (sqlLower.includes(' limit ? offset ?')) {
      const limit = Number(params[params.length - 2]) || orders.length;
      const offset = Number(params[params.length - 1]) || 0;
      return orders.slice(offset, offset + limit).map(mapOrder);
    }

    return orders.map(mapOrder);
  }

  // 插入订单
  if (sqlLower.includes('insert into orders')) {
    const [
      order_number, user_id, status, order_type, table_number,
      delivery_address, delivery_phone, delivery_notes,
      subtotal, tax_amount, discount_amount, delivery_fee,
      total_amount, payment_method, coupon_id, notes, estimated_time
    ] = params;

    const newId = Math.max(0, ...mockData.orders.map(o => o.order_id)) + 1;
    const now = new Date();
    const newOrder = {
      order_id: newId,
      user_id,
      order_number,
      order_type,
      status,
      subtotal: subtotal ?? 0,
      tax_amount: tax_amount ?? 0,
      discount_amount: discount_amount ?? 0,
      delivery_fee: delivery_fee ?? 0,
      total_amount: total_amount ?? 0,
      table_number: table_number ?? null,
      delivery_address: delivery_address ?? null,
      delivery_phone: delivery_phone ?? null,
      delivery_notes: delivery_notes ?? null,
      estimated_delivery_time: estimated_time ?? null,
      notes: notes ?? null,
      coupon_id: coupon_id ?? null,
      payment_method: payment_method ?? null,
      payment_status: 'pending',
      created_at: now,
      updated_at: now
    };
    mockData.orders.push(newOrder);
    return { insertId: newId, affectedRows: 1 };
  }

  // 批量插入订单项
  if (sqlLower.includes('insert into order_items')) {
    const now = new Date();
    const chunk = 6;
    let inserted = 0;
    let firstInsertId = null;
    let nextId = Math.max(0, ...mockData.order_items.map(oi => oi.item_id || 0)) + 1;

    for (let i = 0; i < params.length; i += chunk) {
      const [order_id, menu_item_id, quantity, unit_price, total_price, notes] = params.slice(i, i + chunk);
      const newItem = {
        item_id: nextId++,
        order_id,
        menu_item_id,
        quantity,
        unit_price,
        total_price,
        special_requests: notes ?? null,
        created_at: now
      };
      mockData.order_items.push(newItem);
      inserted++;
      if (firstInsertId == null) firstInsertId = newItem.item_id;
    }

    return { insertId: firstInsertId ?? 0, affectedRows: inserted };
  }

  // 更新订单
  if (sqlLower.includes('update orders')) {
    const idParam = params[params.length - 1];
    const idx = mockData.orders.findIndex(o => String(o.id) === String(idParam));
    if (idx === -1) return { affectedRows: 0 };

    const setIdx = sqlLower.indexOf(' set ');
    const whereIdx = sqlLower.indexOf(' where ');
    const setPart = (setIdx !== -1 && whereIdx !== -1 && whereIdx > setIdx) ? sqlLower.slice(setIdx + 5, whereIdx) : '';

    // 软删除（兼容性）
    if (sqlLower.includes('deleted_at = now()')) {
      mockData.orders[idx].deleted_at = new Date();
      return { affectedRows: 1 };
    }

    // 初始化参数索引
    let p = 0;

    // 解析 SET 片段
    const setFieldIf = (frag, setter) => { if (setPart.includes(frag)) setter(); };

    setFieldIf('status = ?', () => { mockData.orders[idx].status = params[p++]; });
    setFieldIf('order_type = ?', () => { mockData.orders[idx].order_type = params[p++]; });
    setFieldIf('payment_method = ?', () => { mockData.orders[idx].payment_method = params[p++]; });
    setFieldIf('payment_status = ?', () => { mockData.orders[idx].payment_status = params[p++]; });
    setFieldIf('transaction_id = ?', () => { mockData.orders[idx].transaction_id = params[p++]; mockData.orders[idx].payment_id = mockData.orders[idx].transaction_id; });
    setFieldIf('coupon_id = ?', () => { mockData.orders[idx].coupon_id = params[p++]; });
    setFieldIf('notes = ?', () => { mockData.orders[idx].notes = params[p++]; });
    setFieldIf('table_number = ?', () => { mockData.orders[idx].table_number = params[p++]; });
    setFieldIf('delivery_address = ?', () => { mockData.orders[idx].delivery_address = params[p++]; });
    setFieldIf('delivery_phone = ?', () => { mockData.orders[idx].delivery_phone = params[p++]; });
    setFieldIf('delivery_notes = ?', () => { mockData.orders[idx].delivery_notes = params[p++]; });
    setFieldIf('estimated_time = ?', () => { mockData.orders[idx].estimated_time = params[p++]; });

    if (setPart.includes('updated_at = now()') || setPart.includes('updated_at = current_timestamp')) {
      mockData.orders[idx].updated_at = new Date();
    }
    if (setPart.includes('completed_at = now()') || setPart.includes('completed_at = current_timestamp')) {
      mockData.orders[idx].completed_at = new Date();
    }
    if (setPart.includes('cancelled_at = now()') || setPart.includes('cancelled_at = current_timestamp')) {
      mockData.orders[idx].cancelled_at = new Date();
    }
    if (setPart.includes('payment_time = now()') || setPart.includes('payment_time = current_timestamp')) {
      mockData.orders[idx].payment_time = new Date();
    }

    return { affectedRows: 1 };
  }

  // ======= 用户优惠券（含JOIN） =======
  if (sqlLower.includes('select') && sqlLower.includes(' from user_coupons')) {
    // 处理 getUserCoupons: JOIN coupons 并按用户过滤
    const isJoinCoupons = sqlLower.includes(' join coupons');
    let p = 0;
    let rows = mockData.user_coupons.slice();
    // WHERE uc.user_id = ? / user_id = ?
    if (sqlLower.includes(' where uc.user_id = ?') || sqlLower.includes(' where user_id = ?')) {
      const userId = params[p++];
      rows = rows.filter(r => String(r.user_id) === String(userId));
    }
    // AND uc.is_used = ?
    if (sqlLower.includes(' uc.is_used = ?') || sqlLower.includes(' is_used = ?')) {
      const isUsed = params[p++];
      rows = rows.filter(r => Number(r.is_used) === Number(isUsed));
    }

    // 排序
    if (sqlLower.includes(' order by uc.created_at desc') || sqlLower.includes(' order by created_at desc')) {
      rows.sort((a,b) => new Date(b.created_at) - new Date(a.created_at));
    }

    // 如果 JOIN coupons，需要合并券信息字段
    if (isJoinCoupons) {
      rows = rows.map(uc => {
        const c = mockData.coupons.find(c => c.id == uc.coupon_id) || {};
        return {
          ...uc,
          code: c.code || null,
          name: c.title || null,
          name_en: c.title_en || null,
          description: c.description || null,
          description_en: c.description_en || null,
          type: c.discount_type || null,
          value: c.discount_value || null,
          min_order_amount: c.minimum_amount || 0,
          max_discount_amount: c.max_discount || null,
          start_date: c.valid_from || null,
          end_date: c.valid_until || null
        };
      });
    }

    // 分页（LIMIT ? OFFSET ?）
    if (sqlLower.includes(' limit ? offset ?')) {
      const limit = Number(params[params.length - 2]) || rows.length;
      const offset = Number(params[params.length - 1]) || 0;
      return rows.slice(offset, offset + limit);
    }

    return rows;
  }

  // ======= 用户优惠券（纯表查询与统计） =======
  if (sqlLower.includes(' from user_coupons') && sqlLower.includes('select') && !sqlLower.includes(' join ')) {
    let rows = [...mockData.user_coupons];
    let p = 0;

    // 条件过滤
    if (sqlLower.includes(' where ')) {
      if (sqlLower.includes(' user_id = ?')) {
        const userId = params[p++];
        rows = rows.filter(r => r.user_id == userId);
      }
      if (sqlLower.includes(' coupon_id = ?')) {
        const couponId = params[p++];
        rows = rows.filter(r => r.coupon_id == couponId);
      }
      if (sqlLower.includes(' id = ?')) {
        const id = params[p++];
        rows = rows.filter(r => r.id == id);
      }
      if (sqlLower.includes(' is_used = ?')) {
        const isUsed = params[p++];
        rows = rows.filter(r => Number(r.is_used) === Number(isUsed));
      } else if (sqlLower.includes(' is_used = 1')) {
        rows = rows.filter(r => Number(r.is_used) === 1);
      } else if (sqlLower.includes(' is_used = 0')) {
        rows = rows.filter(r => Number(r.is_used) === 0);
      }
      if (sqlLower.includes(' created_at >= ?')) {
        const start = new Date(params[p++]);
        rows = rows.filter(r => new Date(r.created_at) >= start);
      }
      if (sqlLower.includes(' created_at <= ?')) {
        const end = new Date(params[p++]);
        rows = rows.filter(r => new Date(r.created_at) <= end);
      }
    }

    // 计数查询
    if (sqlLower.includes('count(*)')) {
      return [{ count: rows.length }];
    }

    // 仅返回ID
    if (sqlLower.includes('select id')) {
      return rows.map(r => ({ id: r.id }));
    }

    return rows;
  }

  // ======= 用户优惠券插入 =======
  if (sqlLower.includes('insert into user_coupons')) {
    const isIgnore = sqlLower.includes('insert ignore');
    const now = new Date();

    if (sqlLower.includes(' values ') && params.length >= 2) {
      let inserted = 0;
      let firstInsertId = null;

      for (let i = 0; i < params.length; i += 2) {
        const user_id = params[i];
        const coupon_id = params[i + 1];

        const exists = mockData.user_coupons.some(uc => uc.user_id == user_id && uc.coupon_id == coupon_id);
        if (exists) {
          if (isIgnore) continue; // 忽略重复
          throw new Error('Duplicate entry for user_coupons');
        }

        const newId = Math.max(0, ...mockData.user_coupons.map(uc => uc.id)) + 1;
        mockData.user_coupons.push({ id: newId, user_id, coupon_id, is_used: 0, created_at: now, used_at: null });
        inserted++;
        if (firstInsertId == null) firstInsertId = newId;
      }

      return isIgnore && inserted > 1
        ? { affectedRows: inserted }
        : { insertId: firstInsertId ?? 0, affectedRows: inserted };
    }

    return { affectedRows: 0 };
  }

  // ======= 用户优惠券更新 =======
  if (sqlLower.includes('update user_coupons set')) {
    let affected = 0;
    let p = 0;

    const setUsed = sqlLower.includes(' set is_used = 1');
    const setUsedAtNow = sqlLower.includes(' used_at = now()') || sqlLower.includes(' used_at = current_timestamp');

    let idCond = null;
    let userIdCond = null;

    if (sqlLower.includes(' where id = ?')) {
      idCond = params[p++];
    }
    if (sqlLower.includes(' and user_id = ?')) {
      userIdCond = params[p++];
    }

    mockData.user_coupons.forEach(uc => {
      if ((idCond == null || uc.id == idCond) && (userIdCond == null || uc.user_id == userIdCond)) {
        if (setUsed) uc.is_used = 1;
        if (setUsedAtNow) uc.used_at = new Date();
        affected++;
      }
    });

    return { affectedRows: affected };
  }

  // ======= 优惠券 =======
  if (sqlLower.includes('select') && sqlLower.includes(' from coupons')) {
    // 映射到新的Coupon模型字段
    let coupons = mockData.coupons.map(c => ({
      coupon_id: c.id,
      title: c.title,
      description: c.description,
      remarks: c.remarks,
      discount_type: c.discount_type,
      discount_value: c.discount_value,
      minimum_order_amount: c.minimum_order_amount || 0,
      maximum_discount_amount: c.maximum_discount_amount || null,
      validity_type: c.validity_type || 'absolute',
      valid_from: c.valid_from,
      valid_until: c.valid_until,
      validity_days: c.validity_days,
      usage_limit_per_user: c.usage_limit_per_user || 1,
      total_usage_limit: c.total_usage_limit || null,
      eligibility_rules: c.eligibility_rules ? (typeof c.eligibility_rules === 'string' ? c.eligibility_rules : JSON.stringify(c.eligibility_rules)) : '{}',
      inclusion_rules: c.inclusion_rules ? (typeof c.inclusion_rules === 'string' ? c.inclusion_rules : JSON.stringify(c.inclusion_rules)) : '{}',
      exclusion_rules: c.exclusion_rules ? (typeof c.exclusion_rules === 'string' ? c.exclusion_rules : JSON.stringify(c.exclusion_rules)) : '{}',
      time_windows: c.time_windows ? (typeof c.time_windows === 'string' ? c.time_windows : JSON.stringify(c.time_windows)) : '{}',
      status: c.status || 'active',
      total_issued: c.total_issued || 0,
      total_used: c.total_used || 0,
      created_by: c.created_by,
      updated_by: c.updated_by,
      created_at: c.created_at || new Date(),
      updated_at: c.updated_at || new Date(),
      deleted_at: c.deleted_at || null
    }));

    // 支持 WHERE coupon_id = ? 精确查询（优先返回，避免参数索引冲突）
    if (sqlLower.includes(' where coupon_id = ?')) {
      const idParam = params[0];
      let result = coupons.filter(c => c.coupon_id == idParam);
      if (sqlLower.includes('deleted_at is null')) {
        result = result.filter(c => !c.deleted_at);
      }
      return result;
    }

    // 支持 WHERE title = ? 精确查询（优先返回，避免参数索引冲突）
    if (sqlLower.includes(' where title = ?')) {
      const titleParam = params[0];
      let result = coupons.filter(c => c.title === titleParam);
      if (sqlLower.includes('deleted_at is null')) {
        result = result.filter(c => !c.deleted_at);
      }
      return result;
    }

    // 软删除过滤
    if (sqlLower.includes('deleted_at is null')) {
      coupons = coupons.filter(c => !c.deleted_at);
    }

    // 参数索引按出现顺序推进，避免与其他占位符冲突
    let pIndex = 0;

    // status 过滤（支持占位符与字面量）
    if (sqlLower.includes('status = ?')) {
      const statusParam = params[pIndex++];
      coupons = coupons.filter(c => c.status === statusParam);
    } else if (sqlLower.includes("status = 'active'")) {
      coupons = coupons.filter(c => c.status === 'active');
    }

    // 类型过滤
    if (sqlLower.includes('discount_type = ?')) {
      const typeParam = params[pIndex++];
      coupons = coupons.filter(c => c.discount_type === typeParam);
    }

    // 有效期过滤：valid_from <= ? AND valid_until >= ?
    if (sqlLower.includes('valid_from <= ?')) {
      const dt = new Date(params[pIndex++]);
      coupons = coupons.filter(c => !c.valid_from || new Date(c.valid_from) <= dt);
    }
    if (sqlLower.includes('valid_until >= ?')) {
      const dt = new Date(params[pIndex++]);
      coupons = coupons.filter(c => !c.valid_until || new Date(c.valid_until) >= dt);
    }

    // 使用次数限制过滤（基于usage_quota JSON字段）
    if (sqlLower.includes('total_used < total_issued')) {
      coupons = coupons.filter(c => c.total_used < c.total_issued);
    }

    // 排序
    if (sqlLower.includes('order by')) {
      const isDesc = sqlLower.includes(' desc');
      let key = 'created_at';
      if (sqlLower.includes(' order by id')) key = 'id';
      else if (sqlLower.includes(' order by title')) key = 'title';
      else if (sqlLower.includes(' order by created_at')) key = 'created_at';
      else if (sqlLower.includes(' order by updated_at')) key = 'updated_at';
      else if (sqlLower.includes(' order by valid_from')) key = 'valid_from';
      else if (sqlLower.includes(' order by valid_until')) key = 'valid_until';

      coupons.sort((a, b) => {
        const av = a[key];
        const bv = b[key];
        const cmp = av > bv ? 1 : av < bv ? -1 : 0;
        return isDesc ? -cmp : cmp;
      });
    } else {
      coupons.sort((a, b) => new Date(b.created_at) - new Date(a.created_at));
    }

    // 分页（LIMIT ? OFFSET ?）
    if (sqlLower.includes(' limit ? offset ?')) {
      const limit = Number(params[params.length - 2]) || coupons.length;
      const offset = Number(params[params.length - 1]) || 0;
      return coupons.slice(offset, offset + limit);
    }

    return coupons;
  }

  // 优惠券插入
  if (sqlLower.includes('insert into coupons')) {
    const newId = Math.max(...mockData.coupons.map(c => c.id || 0), 0) + 1;

    // 解析INSERT语句中的字段值
    const sqlFields = sql.match(/\(([^)]+)\)\s*VALUES\s*\(([^)]+)\)/i);
    if (sqlFields) {
      const fieldNames = sqlFields[1].split(',').map(f => f.trim());
      const fieldValues = sqlFields[2].split(',').map(v => v.trim());
      
      const newCoupon = { id: newId };
      
      fieldNames.forEach((field, index) => {
        let value = fieldValues[index];
        
        // 处理字符串值（去掉引号）
        if (value.startsWith("'") && value.endsWith("'")) {
          value = value.slice(1, -1);
        }
        
        // 处理NULL值
        if (value === 'NULL') {
          value = null;
        }
        
        // 处理数字值
        if (!isNaN(value) && value !== '') {
          value = Number(value);
        }
        
        newCoupon[field] = value;
      });
      
      // 设置默认时间戳
      newCoupon.created_at = new Date();
      newCoupon.updated_at = new Date();
      
      mockData.coupons.push(newCoupon);
      return { insertId: newId, affectedRows: 1 };
    }
    
    return { insertId: 0, affectedRows: 0 };
  }

  // 优惠券更新
  if (sqlLower.includes('update coupons')) {
    const idParam = params[params.length - 1];
    const couponIndex = mockData.coupons.findIndex(c => c.id == idParam);
    
    if (couponIndex !== -1) {
      // 软删除
      if (sqlLower.includes('deleted_at = now()')) {
        mockData.coupons[couponIndex].deleted_at = new Date();
        return { affectedRows: 1 };
      }

      // 动态字段更新
      const fieldList = [
        'title', 'description', 'remarks', 'discount_type', 'discount_value',
        'validity_type', 'valid_from', 'valid_until', 'relative_days',
        'eligibility_rules', 'inclusion_rules', 'exclusion_rules',
        'time_windows', 'usage_quota', 'eligibility_hash', 'status',
        'total_issued', 'total_used', 'updated_by'
      ];
      let p = 0;
      for (const f of fieldList) {
        if (sqlLower.includes(`${f} = ?`)) {
          let value = params[p++];
          // JSON字段需要字符串化
          if (['eligibility_rules', 'inclusion_rules', 'exclusion_rules', 'time_windows', 'usage_quota'].includes(f) && value && typeof value === 'object') {
            value = JSON.stringify(value);
          }
          mockData.coupons[couponIndex][f] = value;
        }
      }
      mockData.coupons[couponIndex].updated_at = new Date();
      return { affectedRows: 1 };
    }
    return { affectedRows: 0 };
  }

  // ======= 邀请记录 =======
  if (sqlLower.includes('select') && sqlLower.includes('invite_records')) {
    // 统计查询
    if (sqlLower.includes('count(*) as total')) {
      const records = mockData.invite_records;
      return [{ total: records.length }];
    }
    
    if (sqlLower.includes('count(*) as successful')) {
      const successful = mockData.invite_records.filter(r => r.status === 'completed');
      return [{ successful: successful.length }];
    }
    
    if (sqlLower.includes('count(*) as pending')) {
      const pending = mockData.invite_records.filter(r => r.coupon_status === 'partial');
      return [{ pending: pending.length }];
    }
    
    if (sqlLower.includes('count(*) as active')) {
      const active = mockData.invite_records.filter(r => r.status === 'pending');
      return [{ active: active.length }];
    }
    
    if (sqlLower.includes('count(*) as today')) {
      const today = new Date();
      today.setHours(0, 0, 0, 0);
      const todayRecords = mockData.invite_records.filter(r => {
        const inviteDate = new Date(r.invite_date);
        inviteDate.setHours(0, 0, 0, 0);
        return inviteDate.getTime() === today.getTime();
      });
      return [{ today: todayRecords.length }];
    }
    
    if (sqlLower.includes('sum(inviter_coupon_amount + invitee_coupon_amount + bonus_coupon_amount) as totalcoupons')) {
      const total = mockData.invite_records.reduce((sum, r) => {
        return sum + (r.inviter_coupon_amount || 0) + (r.invitee_coupon_amount || 0) + (r.bonus_coupon_amount || 0);
      }, 0);
      return [{ totalCoupons: total }];
    }
    
    // 普通查询 - 返回邀请记录列表
    let records = [...mockData.invite_records];
    
    // 支持分页
    if (sqlLower.includes('limit')) {
      const limitMatch = sql.match(/limit\s+(\d+)(?:\s+offset\s+(\d+))?/i);
      if (limitMatch) {
        const limit = parseInt(limitMatch[1]);
        const offset = limitMatch[2] ? parseInt(limitMatch[2]) : 0;
        records = records.slice(offset, offset + limit);
      }
    }
    
    return records;
  }

  // 未匹配的 SQL，返回空
  return [];
};

// 为兼容 mysql2 接口，提供 query/execute/transaction 等方法
const query = async (sql, params = []) => {
  const rows = await mockQuery(sql, params);
  return rows;
};

const execute = async (sql, params = []) => {
  const rows = await mockQuery(sql, params);
  return [rows, []];
};

const transaction = async (callback) => {
  const connection = {
    execute: async (sql, params = []) => {
      const rows = await mockQuery(sql, params);
      return [rows, []];
    },
    query: async (sql, params = []) => {
      const rows = await mockQuery(sql, params);
      return [rows, []];
    },
    beginTransaction: async () => {},
    commit: async () => {},
    rollback: async () => {},
    release: () => {}
  };
  return await callback(connection);
};

const testConnection = async () => true;
const closePool = async () => {};
const getConnection = async () => ({
  execute: async (sql, params = []) => {
    const rows = await mockQuery(sql, params);
    return [rows, []];
  },
  query: async (sql, params = []) => {
    const rows = await mockQuery(sql, params);
    return [rows, []];
  },
  beginTransaction: async () => {},
  commit: async () => {},
  rollback: async () => {},
  release: () => {}
});

module.exports = {
  query,
  execute,
  transaction,
  testConnection,
  closePool,
  getConnection,
  mockData
};